package dao;

import model.Student;
import utils.DBUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**关于学生表的DAO层操作
 * @author tongchen
 * @create 2022-12-28 13:19
 */
public class StudentDao {
    //创建变量
    Connection connection=null;
    PreparedStatement statement=null;
    ResultSet resultSet=null;

 public int insert(Student student){
     //建立连接
     try {
          connection = DBUtils.getConnection();
          //sql语句
         String sql="insert into student (sn,name,qq_email,classes_id)values(?,?,?,?)";
         //创建执行器
          statement= connection.prepareStatement(sql);
          statement.setInt(1,student.getSn());
          statement.setString(2,student.getName());
          statement.setString(3,student.getEmail());
          statement.setInt(4,student.getClassId());
          //执行sql
         int i = statement.executeUpdate();
         return i;
     } catch (SQLException e) {
         throw new RuntimeException(e);
     }finally {
         //释放资源
         DBUtils.closeResources(connection,statement,resultSet);
     }


 }
 public List<Student> selectOne(String name){
     //建立连接
     try {
        connection= DBUtils.getConnection();
        //sql语句
         String sql="select id,sn,name,qq_email,classes_id from student where name =?";
        //创建执行对象
        statement= connection.prepareStatement(sql);
        //补齐参数
         statement.setString(1,name);
         //执行并获取结果
          resultSet = statement.executeQuery();
         List<Student> students = null;
         while(resultSet.next()){
             if(students==null){
                 students=new ArrayList<>();
             }
             Student student = new Student();

             student.setId(resultSet.getInt(1));
             student.setSn(resultSet.getInt(2));
             student.setName(resultSet.getString(3));
             student.setEmail(resultSet.getString(4));
             student.setClassId(resultSet.getInt(5));
             // 把学生对象加入集合
             students.add(student);
         }
         return students;
     } catch (SQLException e) {
         throw new RuntimeException(e);
     }finally {
         //释放资源
         DBUtils.closeResources(connection,statement,resultSet);
     }
 }
 public List<Student>selectAll(Integer classid){
     //获取连接
     try {
         connection=DBUtils.getConnection();
         //编写sql
         String sql="select id ,sn ,name,qq_email ,classes_id from student where classes_id=?";
         //创建执行器
         statement=connection.prepareStatement(sql);
         //补全sql
         statement.setInt(1,classid);
         //执行sql
         resultSet=statement.executeQuery();
         ArrayList<Student>students=null;
         while(resultSet.next()) {
             if (students == null) {
                 students = new ArrayList<>();
             }
             Student student = new Student();
             student.setId(resultSet.getInt(1));
             student.setSn(resultSet.getInt(2));
             student.setName(resultSet.getString(3));
             student.setEmail(resultSet.getString(4));
             student.setClassId(resultSet.getInt(5));
             // 把学生对象加入集合
             students.add(student);

         }
         return students;
     } catch (SQLException e) {
         throw new RuntimeException(e);
     }finally {
         DBUtils.closeResources(connection,statement,resultSet);
     }
 }
 public int updateClass (int id ,int classId){
     //通过学生id修改学生信息
     try {
        connection= DBUtils.getConnection();
        //创建sql
         String sql="update student set classes_id=? where id=?";
         //创建statement
         statement=connection.prepareStatement(sql);
         //补齐参数
         statement.setInt(1,classId);
         statement.setInt(2,id);
         //执行并返回结果
         int i = statement.executeUpdate();
         return i;
     } catch (SQLException e) {
         throw new RuntimeException(e);
     }
 }
 //删除操作
    public int delete(String name){
     //建立连接
        try {
            connection= DBUtils.getConnection();
            String sql="delete from student where name =?";
            statement = connection.prepareStatement(sql);
            //补全sql
            statement.setString(1,name);
            int i = statement.executeUpdate();
            return i;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBUtils.closeResources(connection,statement,resultSet);
        }
    }
}
